package me.keiwu.admin.dao;


import me.keiwu.admin.domain.user.UserLabel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public interface UserLabelRepository extends JpaRepository<UserLabel, Long>, JpaSpecificationExecutor<UserLabel> {

    //根据id删除
    @Query(value = "delete from user_label where label_id=?1 ", nativeQuery = true)
    @Modifying
    int deleteByLabelId(long labelId);

    @Query(value = "delete from user_label where user_id=?1 and label_id=?2", nativeQuery = true)
    @Modifying
    int deleteByUserIdLabelId(long userlId, long labelId);




    @Query(value="SELECT count(distinct user_id) FROM user_label WHERE label_id = ?1", nativeQuery=true)
    int countDistinctUserByLabelId(Long labelId);

    @Query(value="SELECT distinct user_id FROM user_label WHERE label_id = ?1 limit ?2 offset ?3", nativeQuery=true)
    List<Long> findDistinctUserByLabelId(Long labelId, Integer size, Integer offset);




    @Query(value="SELECT distinct user_id FROM user_label WHERE label_id = ?1", nativeQuery=true)
    List<Long> findDistinctUserByLabelId(Long labelId);







//    @Query(value="SELECT  distinct my_user.user_id " +
//            " FROM my_user " +
//            "WHERE  my_user.user_id not in (select user_label.user_id from user_label where user_label.label_id = ?1) " +
//            " limit ?2  offset ?3" ,
//            nativeQuery=true)
//    List<Long> findUserWithoutLabelId(Long labelId, Integer size, Integer offset);
//
//    @Query(value="SELECT  count(distinct my_user.user_id) " +
//            " FROM my_user " +
//            "WHERE  my_user.user_id not in (select user_label.user_id from user_label where user_label.label_id = :labelId) " ,
//            nativeQuery=true)
//    int findCountUserWithoutLabelId(@Param("labelId")  long labelId);
//
//
//
//
//    @Query(value="SELECT  distinct my_user.user_id " +
//            " FROM my_user " +
//            "WHERE  my_user.user_id not in (select user_label.user_id from user_label where user_label.label_id = ?1) and my_user.nick_name like CONCAT(?2, '%') " +
//            " limit ?3  offset ?4" ,
//            nativeQuery=true)
//    List<Long> findUserWithoutLabelIdWithNickName(Long labelId, String nickName, Integer size, Integer offset);
//
//    @Query(value="SELECT  count(distinct my_user.user_id) " +
//            " FROM my_user " +
//            "WHERE  my_user.user_id not in (select user_label.user_id from user_label where user_label.label_id = :labelId) and my_user.nick_name like CONCAT(:nickName, '%') " ,
//            nativeQuery=true)
//    int findCountUserWithoutLabelIdWithNickName(@Param("labelId")  long labelId, @Param("nickName")  String nickName);



    Iterable<UserLabel> findAllByUserId(Long userId);

}
